<?php
/**
 * Created by PhpStorm.
 * User: BlueIdeaLirui
 * Date: 2019/11/11
 * Time: 10:13
 */

namespace business\bigdata\repositories;

use yii\db\Query;

class MaterialRepository
{
    protected $connection;

    public function __construct()
    {
        $this->connection = \Yii::$app->get('dbSupplierBigdata');
    }

    /**
     * 返回材料总数
     * @return mixed
     */
    public function getMaterialCount()
    {
        $sql = <<<sql
            SELECT count(DISTINCT material_name) 
            FROM mycommunity_contractor_bigdata.yl_m_material_basic 
            WHERE is_deleted = 0
sql;
        return $this->connection->createCommand($sql)
            ->queryScalar();
    }

    /**
     * 从大数据表中获取变更的供应商id
     * @param $offset
     * @param $pageSize
     * @return array
     */
    public function getAllMaterialId($offset, $pageSize)
    {
        $sql = <<<sql
            SELECT material_sku_code
            FROM mycommunity_contractor_bigdata.yl_m_material_basic 
            WHERE material_sku_code > :offset AND is_deleted = 0
            order by material_sku_code ASC
            LIMIT :pageSize
sql;
        $result = $this->connection->createCommand($sql)
            ->bindValue(':offset', $offset)
            ->bindValue(':pageSize', $pageSize)
            ->queryColumn();
        return $result;
    }

    /**
     * 返回材料名
     * @return mixed
     */
    public function getAllMaterialName($offset, $pageSize)
    {
        $sql = <<<sql
            SELECT material_name
            FROM mycommunity_contractor_bigdata.yl_m_material_basic 
            WHERE is_deleted = 0
            group by material_name
            order by material_name ASC
            LIMIT :pageSize
            OFFSET :offset
sql;
        $result = $this->connection->createCommand($sql)
            ->bindValue(':offset', $offset)
            ->bindValue(':pageSize', $pageSize)
            ->queryColumn();
        return $result;
    }

    /**
     * 根据材料名称获取材料基本信息
     * @param array $materialNames
     * @return array
     * @throws \yii\base\InvalidConfigException
     * @throws \yii\db\Exception
     */
    public function getMaterialBasicInfoByNames(array $materialNames)
    {
        $query = new Query();
        $query->select('*')->from('mycommunity_contractor_bigdata.yl_m_material_basic')->where(['in', 'material_name', $materialNames]);
        $command = $query->createCommand(\Yii::$app->get('dbSupplierBigdata'));
        $data = $command->queryAll();

        $result = [];
        foreach ($data as $dv) {
            $tmpKey = md5($dv['area_code'].$dv['material_name'].$dv['material_category_code']);
            if (array_key_exists($tmpKey, $result)) {
                $result[$tmpKey]['materialSkuCodes'][] = $dv['material_sku_code'];
                $result[$tmpKey]['materialSpecs'][] = $dv['material_spec'];
            } else {
                $tmpArr = [
                    'uid'=>$tmpKey,
                    'areaCode'=>$dv['area_code'],
                    'areaName'=>$dv['area_name'],
                    'materialName'=>$dv['material_name'],
                    'materialCategoryCode'=>$dv['material_category_code'],
                    'materialCategory'=>$dv['material_category'],
                    'materialCategoryAlias'=>$dv['material_category'],
                    'materialSkuCodes'=>[$dv['material_sku_code']],
                    'materialSpecs'=>[$dv['material_spec']],
                ];
                $result[$tmpKey] = $tmpArr;
            }
        }
        return array_values($result);
    }

    /**
     * 获取材料价格
     * @param $materialSkuCodes
     * @return array
     * @throws \yii\db\Exception
     */
    public function getMaterialPrice($materialSkuCodes)
    {
        $query = new Query();

        $query->select('material_sku_code, material_unit, material_spec, tax_price, no_tax_price, date')
            ->from('mycommunity_contractor_bigdata.yl_m_material_price')
            ->where(['in', 'material_sku_code', $materialSkuCodes])
            ->orderBy(['date'=>SORT_DESC]);

        $result = $query->createCommand($this->connection)->queryAll();

        return $result;
    }
}
